/*
 * To change this license header, choose License Headers in Project Properties.
 * To change this template file, choose Tools | Templates
 * and open the template in the editor.
 */
package ac.lib.database;

import java.io.File;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.IOException;
import java.io.InputStream;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.Arrays;
import java.util.logging.Level;
import java.util.logging.Logger;
import javax.swing.ImageIcon;

/**
 *
 * @author ismail
 */
public class operations extends dbconnection {

    //<editor-fold defaultstate="collapsed" desc="getData[select statment]">
    private ResultSet getDataFull(String sql) {
        //  this.openCon();

        try {

            st = con.createStatement();
            retuensqlString += "--" + sql;
            rs = st.executeQuery(sql);

        } catch (SQLException ex) {
            retuenErrorMessage += ex.getMessage();

            ac.ui.utils.sendError(null, "xxx", ex.getMessage());
        }
        return rs;
    }

    private ResultSet getDataFull(String table, String Cdata, String Where) {
        //  this.openCon();

        if (Cdata.trim().equals("")) {
            Cdata = "*";
        }

        if (!Where.equals("")) {
            sql = "SELECT " + Cdata + " FROM " + table + " WHERE " + Where;
        } else {

            sql = "SELECT " + Cdata + " FROM " + table;
        }
        rs = getDataFull(sql);

        return rs;
    }

    public String[] getQuerygetColumsTitleswithoutConect(String query) {

        String[] returnArray = null;

        try {

            this.getDataFull(query);
            resMetaData = rs.getMetaData();
            int clu_num = resMetaData.getColumnCount();

            returnArray = new String[clu_num];
            for (int i = 0; i < clu_num; i++) {
                returnArray[i] = resMetaData.getColumnName(i);
            }

        } catch (SQLException ex) {
        }

        return returnArray;
    }

    public String[] getQuerygetColumsTitles(String query) {

        String[] returnArray = null;
        setOpenCon();
        try {

            this.getDataFull(query);
            resMetaData = rs.getMetaData();
            int clu_num = resMetaData.getColumnCount();

            returnArray = new String[clu_num];
            for (int c = 0; c < clu_num; c++) {
                returnArray[c] = resMetaData.getColumnName(c + 1);
            }

        } catch (SQLException ex) {

            retuenErrorMessage += ex.getMessage();
            System.out.printf(ex.getMessage());
        }
        setCloseCon();
        return returnArray;
    }

    public ResultSet getQueryWithOutCon(String sql) {
        rs = this.getDataFull(sql);
        return rs;
    }

    public ResultSet getDataWithOutCon(String table) {
        rs = this.getDataFull(table, "*", "");
        return rs;
    }

    public ResultSet getDataWithOutCon(String table, String Cdata) {
        this.getDataFull(table, Cdata, "");
        return rs;
    }

    public ResultSet getDataWithOutCon(String table, String Cdata, String Where) {
        this.getDataFull(table, Cdata, Where);
        return rs;
    }

    public ResultSet getQueryData(String query) {

        this.setOpenCon();
        this.getDataFull(query);
        this.setCloseCon();
        return rs;
    }

    public ResultSet getQueryDataWithOutCon(String query) {
        this.getDataFull(query);
        return rs;
    }

    public ResultSet getData(String table) {

        this.setOpenCon();
        this.getDataFull(table, "*", "");
        this.setCloseCon();
        return rs;
    }

    public ResultSet getData(String table, String Cdata) {

        this.setOpenCon();
        this.getDataFull(table, Cdata, "");
        this.setCloseCon();
        return rs;
    }

    public ResultSet getData(String table, String Cdata, String Where) {

        this.setOpenCon();
        this.getDataFull(table, Cdata, Where);
        this.setCloseCon();
        return rs;
    }
////////////////////////////////////////////////////////////////////////// ROW

//</editor-fold>
    //<editor-fold defaultstate="collapsed" desc="get Row Cont[Row Count]">
    private int RowCount(String table, String Where) {
        int rowCount = 0;
        try {
            rs = getDataFull(table, "", Where);
            if (rs != null) {

                rs.last();
                rowCount = rs.getRow();
            }

        } catch (SQLException ex) {

            retuenErrorMessage += ex.getMessage();
        }
        return rowCount;
    }

    public int getRowCount(String table, String Where) {

        int returnData;
        this.setOpenCon();
        returnData = this.RowCount(table, Where);
        this.setOpenCon();
        return returnData;

    }

    public int getRowCount(String table) {
        int returnData;
        this.setOpenCon();
        returnData = this.RowCount(table, "");
        this.setOpenCon();
        return returnData;

    }

    public int getRowCountWithOutCon(String table, String Where) {
        int returnData;
        returnData = this.RowCount(table, Where);
        return returnData;
    }

    public int getRowCountWithOutCon(String table) {
        int returnData;
        returnData = this.RowCount(table, "");
        return returnData;
    }

//</editor-fold>
    //<editor-fold defaultstate="collapsed" desc="get Colum[select statment]">
    private String[] getFullColumn(String table, String Cdata, String Where) {

        int clu_num = getRowCount(table, Where);
        String[] returnArray = null;
        int i = 0;

        try {
            rs = getDataFull(table, Cdata, Where);
            if (rs != null) {
                returnArray = new String[clu_num];
                while (rs.next()) {
                    returnArray[i] = rs.getString(Cdata);
                    i++;
                }
            }

        } catch (SQLException ex) {
            retuenErrorMessage += ex.getMessage();
        }
        return returnArray;
    }

    public String[] getColumn(String table, String Cdata) {
        this.setOpenCon();
        String[] returnData = this.getFullColumn(table, Cdata, "");
        this.setCloseCon();
        return returnData;
    }

    public String[] getColumn(String table, String Cdata, String Where) {
        this.setOpenCon();
        String[] returnData = this.getFullColumn(table, Cdata, Where);
        setCloseCon();
        return returnData;
    }

    public String[] getColumnWithOutCon(String table, String Cdata) {
        String[] returnData = this.getFullColumn(table, Cdata, "");
        return returnData;
    }

    public String[] getColumnWithOutCon(String table, String Cdata, String Where) {
        String[] returnData = this.getFullColumn(table, Cdata, Where);
        return returnData;
    }

    ////////////////////////////////getFullColumn
//</editor-fold>
    //<editor-fold defaultstate="collapsed" desc="get Row [select Statment]">
    public ImageIcon getimg(String table, String cdata, String Where) {
        this.setOpenCon();

        ImageIcon returnData = this.FullImage(table, cdata, Where);
        this.setCloseCon();
        return returnData;
    }

    public ImageIcon getimg(String table, String cdata, int id) {
        this.setOpenCon();
        String pid = Integer.toString(id);
        ImageIcon returnData = this.FullImage(table, cdata, "id=" + pid);
        this.setCloseCon();
        return returnData;
    }

    private ImageIcon FullImage(String table, String Cdata, String Where) {

        ImageIcon imageIcon = null;

        try {

            rs = this.getDataFull(table, Cdata, Where);

            if (rs.next()) {

                com.mysql.jdbc.Blob pic = (com.mysql.jdbc.Blob) rs.getBlob(1);
                imageIcon = new ImageIcon(pic.getBytes(1L, (int) pic.length()));
            }

        } catch (SQLException ex) {
            ac.ui.utils.sendError(null, table, ex.getMessage());
        }

        return imageIcon;
    }

    private String[] FullRow(String table, String Cdata, String Where) {

        String[] returnArray = null;

        try {

            this.getDataFull(table, Cdata, Where);
            resMetaData = rs.getMetaData();
            int clu_num = resMetaData.getColumnCount();
            returnArray = new String[clu_num];

            int mu = 0;

            if (rs.next()) {
                for (int c = 0; c < clu_num; c++) {

                    returnArray[c] = rs.getString(c + 1);

                }
            }

        } catch (SQLException ex) {
        }

        return returnArray;
    }

    public String[] getRow(String table) {
        this.setOpenCon();
        String[] returnData = this.FullRow(table, "", "");

        this.setCloseCon();
        return returnData;
    }

    public String[] getRow(String table, String cdata, String where) {
        this.setOpenCon();
        String[] returnData = this.FullRow(table, cdata, where);
        this.setCloseCon();
        return returnData;
    }

    public String[] getRow(String table, String Where) {
        this.setOpenCon();
        String[] returnData = this.FullRow(table, "", Where);
        this.setCloseCon();
        return returnData;
    }

    public String[] getRow(String table, String cdata, int id) {
        this.setOpenCon();
        String pid = Integer.toString(id);
        String[] returnData = this.FullRow(table, cdata, "id=" + pid);
        this.setCloseCon();
        return returnData;
    }

    public String[] getRow(String table, int id) {
        this.setOpenCon();
        String pid = Integer.toString(id);
        String[] returnData = this.FullRow(table, "", "id=" + pid);
        this.setCloseCon();
        return returnData;
    }

    public String[] getRowWithOutCon(String table) {
        String[] returnData = this.FullRow(table, "", "");
        return returnData;
    }

    public String[] getRowWithOutCon(String table, String cdata, String where) {
        String[] returnData = this.FullRow(table, cdata, where);
        return returnData;
    }

    public String[] getRowWithOutCon(String table, String Where) {
        String[] returnData = this.FullRow(table, "", Where);
        return returnData;
    }

    public String[] getRowWithOutCon(String table, int id) {
        String pid = Integer.toString(id);
        String[] returnData = this.FullRow(table, "", "id=" + pid);
        return returnData;
    }
//</editor-fold>
    //<editor-fold defaultstate="collapsed" desc="get Value [select Statment]">

    private String getFullValue(String table, String Cdata, String Where) {

        String return_string = "";
        try {

            rs = this.getDataFull(table, Cdata, Where);
            if (rs.next()) {

                return_string = rs.getString(Cdata);
            }

        } catch (SQLException ex) {
        }

        return return_string;

    }

    public String getValue(String table, String Cdata, int id) {
        this.setOpenCon();
        String returnData = this.getFullValue(table, Cdata, " where id='" + id + "'");
        this.setCloseCon();
        return returnData;
    }

    public String getValue(String table, String Cdata, String Where) {
        this.setOpenCon();
        String returnData = this.getFullValue(table, Cdata, Where);
        this.setCloseCon();
        return returnData;
    }

    public String getValueWithOutCon(String table, String Cdata, int id) {
        String returnData = this.getFullValue(table, Cdata, " where id='" + id + "'");
        return returnData;
    }

    public String getValueWithOutCon(String table, String Cdata, String Where) {

        String returnData = this.getFullValue(table, Cdata, Where);
        return returnData;
    }
//</editor-fold>

    //<editor-fold defaultstate="collapsed" desc="Delete ">
    private String Delete(String table, String Where, String id) {
        retuenstatus = 0;

        try {
            st = con.createStatement();

            if (!Where.equals("")) {
                sql = "Delete  From " + table + " Where " + Where;
            } else {
                sql = "Delete  From " + table + " Where id=" + id;
            }

            retuensqlString = sql;
            retuenstatus = st.executeUpdate(sql);
        } catch (SQLException ex) {
            retuenErrorMessage += "--" + ex.getMessage();
            ac.ui.utils.sendError(null, table, ex.getMessage());

        }

        return "";
    }

    public String setDelete(String table, int id) {
        this.setOpenCon();
        String pid = Integer.toString(id);
        String returndata = this.Delete(table, "", pid);
        this.setCloseCon();
        return returndata;

    }

    public String setDelete(String table, String Where) {
        this.setOpenCon();
        String returndata = this.Delete(table, Where, "");
        this.setCloseCon();
        return returndata;

    }

    public String setDeleteWithOutCon(String table, int id) {

        String pid = Integer.toString(id);
        String returndata = this.Delete(table, "", pid);

        return returndata;

    }

    public String setDeleteWithOutCon(String table, String Where) {

        String returndata = this.Delete(table, Where, "");

        return returndata;

    }
//</editor-fold>
    //<editor-fold defaultstate="collapsed" desc="update">

    private String setFullUpdate(String table, String[][] data, String Where, String id) {

        String valus = "";
        retuenstatus = 0;
        for (int i = 0; i < data[0].length; i++) {
            String spli = ",";
            if (i == 0) {
                spli = "";
            }
            if (data[1][i].indexOf("'") == -1) {

                if (!data[1][i].equals("")) {
                    if (!data[1][i].equals("")) {
                        valus += spli + "`" + data[0][i] + "` = " + "'" + data[1][i] + "'";
                    }

                }
            } else {
                if (!data[1][i].equals("")) {
                    valus += spli + "`" + data[0][i] + "` = " + data[1][i];
                }
            }
        }

        try {
            st = con.createStatement();

            if (!Where.equals("")) {

                sql = "Update  `" + table + "`  set " + valus + " Where " + Where;
            } else {

                sql = "Update  `" + table + "`  set " + valus + " Where id=" + id;

            }

            retuensqlString += "--" + sql;

            retuenstatus = st.executeUpdate(sql);

        } catch (SQLException ex) {
            ac.ui.utils.sendError(null, table, ex.getMessage());

            retuenErrorMessage += "--" + ex.getMessage();
        }

        return "";
    }

    public String setUpdate(String table, String[][] data, int id) {
        this.setOpenCon();
        String pid = Integer.toString(id);
        String returndata = this.setFullUpdate(table, data, "", pid);
        this.setCloseCon();
        return returndata;
    }

    public String setUpdate(String table, String[][] data, String Where) {
        this.setOpenCon();
        String returndata = this.setFullUpdate(table, data, Where, "");
        this.setCloseCon();
        return returndata;

    }

    public String setUpdateWithOutCon(String table, String[][] data, int id) {
        String pid = Integer.toString(id);
        String returndata = this.setFullUpdate(table, data, "", pid);
        return returndata;
    }

    public String setUpdateWithOutCon(String table, String[][] data, String Where) {
        String returndata = this.setFullUpdate(table, data, Where, "");
        return returndata;

    }
//</editor-fold>
    //<editor-fold defaultstate="collapsed" desc="Update Value">

    private String setFullUpdateValue(String table, String fieldName, String Value, String Where, String id) {
        retuenstatus = 0;
        try {
            st = con.createStatement();
            if (!Where.equals("")) {
                sql = "Update  `" + table + "`  set " + fieldName + "='" + Value + "' Where " + Where;
            } else {
                sql = "Update  `" + table + "`  set " + fieldName + "='" + Value + "' Where id=" + id;
            }

            retuensqlString += "--" + sql;
            retuenstatus = st.executeUpdate(sql);

        } catch (SQLException ex) {
            ac.ui.utils.sendError(null, table, ex.getMessage());

            retuenErrorMessage += "--" + ex.getMessage();
        }

        return "";
    }

    public String setUpdateValue(String table, String fieldName, String Value, int id) {
        this.setOpenCon();
        String pid = Integer.toString(id);
        String returndata = this.setFullUpdateValue(table, fieldName, Value, "", pid);
        this.setCloseCon();
        return returndata;
    }

    public String setUpdateValue(String table, String fieldName, String Value, String Where) {
        this.setOpenCon();
        String returndata = this.setFullUpdateValue(table, fieldName, Value, Where, "");
        this.setCloseCon();
        return returndata;

    }

    public String setUpdateValueWithOutCon(String table, String fieldName, String Value, int id) {

        String pid = Integer.toString(id);
        String returndata = this.setFullUpdateValue(table, fieldName, Value, "", pid);

        return returndata;
    }

    public String setUpdateValueWithOutCon(String table, String fieldName, String Value, String Where) {

        String returndata = this.setFullUpdateValue(table, fieldName, Value, Where, "");

        return returndata;

    }

//</editor-fold>
    //<editor-fold defaultstate="collapsed" desc="Insert">
    private String Insert(String table, String[][] data) {

        retuenstatus = 0;
        String DBflds = "";
        String valus = "";

        for (int i = 0; i < data[0].length; i++) {
            String spli = ",";
            if (i == 0) {
                spli = "";
            }
            DBflds += spli + "`" + data[0][i] + "`";
            valus += spli + "'" + data[1][i] + "'";

        }

        try {
            st = con.createStatement();

            sql = "INSERT INTO `" + table + "` (" + DBflds + ") VALUES (" + valus + ")";

            retuensqlString += "--" + sql;
            retuenstatus = st.executeUpdate(sql);

        } catch (SQLException ex) {

            ac.ui.utils.sendError(null, table, ex.getMessage());
            retuenErrorMessage = ex.getMessage();

        }

        return "";

    }

    public void setInsert(String table, String[][] data) {

        this.setOpenCon();
        this.Insert(table, data);
        this.setCloseCon();

    }

    public void setInsertWithOutCon(String table, String[][] data) {
        this.Insert(table, data);

    }
//</editor-fold>

    //<editor-fold defaultstate="collapsed" desc="View">
    private void getVaiwFull(String table, String name, String Cdata, String Where, String sqlquery) {
        //  this.openCon();

        try {
            if (Cdata.trim().equals("")) {
                Cdata = "*";
            }

            if (!Where.equals("")) {
                sql = "CREATE OR REPLACE  VIEW " + name + " AS SELECT " + Cdata + " FROM " + table + " WHERE " + Where;
            } else {

                sql = "CREATE OR REPLACE  VIEW " + name + " AS  SELECT " + Cdata + " FROM " + table;
            }
            if (!sqlquery.equals("")) {

                sql = "CREATE OR REPLACE  VIEW " + name + " AS  " + sqlquery;

            }

            st = con.createStatement();
            retuensqlString += "--" + sql;
            st.executeUpdate(sql);

        } catch (SQLException ex) {

            retuenErrorMessage += ex.getMessage();
            ac.ui.utils.sendError(null, table, ex.getMessage());

        }

    }

    public void ctareViewData(String table, String name) {

        this.setOpenCon();
        getVaiwFull(table, name, "*", "", "");
        this.setCloseCon();

    }

    public ResultSet ctareViewData(String table, String name, String Cdata) {

        this.setOpenCon();
        this.getVaiwFull(table, name, Cdata, "", "");
        this.setCloseCon();
        return rs;
    }

    public ResultSet ctareViewDataFromQuery(String filename, String SQL) {

        this.setOpenCon();
        this.getVaiwFull("", filename, "", "", SQL);
        this.setCloseCon();
        return rs;
    }

    public void ctareViewData(String table, String name, String Cdata, String Where) {

        this.setOpenCon();
        this.getVaiwFull(table, name, Cdata, Where, "");
        this.setCloseCon();

    }

    public void ctareViewRowQuery(String table, String name, int id) {
        this.setOpenCon();
        String pid = Integer.toString(id);
        this.getVaiwFull(table, name, "", "id=" + pid, "");
        this.setCloseCon();

    }

    public void ctareViewRow(String table, String name, int id) {
        this.setOpenCon();
        String pid = Integer.toString(id);
        this.getVaiwFull(table, name, "", "id=" + pid, "");
        this.setCloseCon();

    }

    public void ctareViewRow(String table, String name, String cdata, int id) {
        this.setOpenCon();
        String pid = Integer.toString(id);
        this.getVaiwFull(table, name, cdata, "id=" + pid, "");
        this.setCloseCon();

    }

    //</editor-fold>
    //<editor-fold defaultstate="collapsed" desc="Export & import">
    //        String connectionString = "jdbc:mysql://" + op.getProperty("DBHost") + "/" + op.getProperty("DBname")+"?useUnicode=true&characterEncoding=utf8";
    public void setExportDb(String filePath) {
        retuenuseData = "";
        retuensqlString = "";
        retuenErrorMessage = "";

        String dumpCommand = "mysqldump -u " + DBUser + " -p" + DBPassword + " " + DBName + "  --default-character-set=utf8 --result-file=" + filePath;

        retuenuseData = filePath;
        retuensqlString = dumpCommand;

        try {
            Runtime rt = Runtime.getRuntime();

            //  String command = URLEncoder.encode(dumpCommand, "utf-8");
            Process proc = rt.exec(dumpCommand);
        } catch (IOException ex) {
            retuenErrorMessage += ex.getMessage();
        }

    }

    public void setImportDb(String filePath) {
        retuenuseData = "";
        retuensqlString = "";
        retuenErrorMessage = "";
        String[] executeCmd = new String[]{"mysql", DBName, "-u" + DBUser, "-p" + DBPassword, "-e", " source " + filePath};
        retuenuseData = filePath;

        retuensqlString = Arrays.deepToString(executeCmd);
        try {
            Runtime rt = Runtime.getRuntime();
            Process proc = rt.exec(executeCmd);
        } catch (IOException ex) {
            retuenErrorMessage += ex.getMessage();
        }

    }

//</editor-fold>
    PreparedStatement psmnt = null;
    FileInputStream fis = null;

    public void insertV2(String table, String[] fields, String[] values, String[] types) {
        String DBflds = "";
        String valus = "1";
        String DBflds2 = "";
        this.setOpenCon();
        retuenstatus = 0;

        for (int i = 0; i < fields.length; i++) {
            String spli = ",";
            if (i == 0) {
                spli = "";
            }
            DBflds += spli + "`" + fields[i] + "`";
            DBflds2 += spli + "?";

        }

        try {
            con.setAutoCommit(false);
            String s55 = "insert into  " + table + " (" + DBflds + ") " + "values(" + DBflds2 + ")";
            psmnt = con.prepareStatement(s55);

            for (int i = 0; i < values.length; i++) {

                int t = i + 1;
                valus = values[i];
                if (types[i].equals("file")) {

                    File image = new File(valus);
                    try {
                        fis = new FileInputStream(image);
                    } catch (FileNotFoundException ex) {

                        ac.ui.utils.sendError(null, table, ex.getMessage());
                    }

                    psmnt.setBinaryStream(t, (InputStream) fis, (int) (image.length()));

                } else {

                    psmnt.setString(t, valus);

                }
            }
            retuenstatus = psmnt.executeUpdate();
            con.commit();

        } catch (SQLException ex) {

            ac.ui.utils.sendError(null, table, ex.getMessage());

        }
        this.setCloseCon();
    }

    public void editV2(String table, String[] fields, String[] values, String[] types, String id, String Where) throws FileNotFoundException {

        retuenstatus = 0;
        String DBflds = "";
        String valus = "1";
        String DBflds2 = "";
        this.setOpenCon();

        for (int i = 0; i < fields.length; i++) {
            String spli = ",";
            if (i == 0) {
                spli = "";
            }
            valus = values[i];
            if (!valus.equals("") && !valus.equals("null")) {
                DBflds += spli + "`" + fields[i] + "`=?";
            }

        }

        try {
            con.setAutoCommit(false);
            String s55 = "";
            if (!Where.equals("")) {

                s55 = " update    " + table + "  SET  " + DBflds + " Where " + Where;
            } else {

                s55 = " update    " + table + "  SET  " + DBflds + " Where id=" + id;

            }

            psmnt = con.prepareStatement(s55);
            int t = 1;
            for (int i = 0; i < values.length; i++) {

                valus = values[i];

                if (!valus.equals("") && !valus.equals("null")) {

                    if (types[i].equals("file")) {

                        File image = new File(valus);
                        fis = new FileInputStream(image);

                        psmnt.setBinaryStream(t, (InputStream) fis, (int) (image.length()));

                    } else {

                        psmnt.setString(t, valus);
                    }

                    t++;
                }

            }
            retuenstatus = psmnt.executeUpdate();
            con.commit();

        } catch (SQLException ex) {

            ac.ui.utils.sendError(null, table, ex.getMessage());

        }
        this.setCloseCon();
    }

    public void setUpdatev2(String table, String[] fields, String[] values, String[] types, String Where) {
        this.setOpenCon();
        try {
            this.editV2(table, fields, values, types, "", Where);
        } catch (FileNotFoundException ex) {
            Logger.getLogger(operations.class.getName()).log(Level.SEVERE, null, ex);
        }
        this.setCloseCon();

    }

    public void setUpdatev2(String table, String[] fields, String[] values, String[] types, int id) {
        this.setOpenCon();
        try {
            this.editV2(table, fields, values, types, "" + id, "");
        } catch (FileNotFoundException ex) {
            Logger.getLogger(operations.class.getName()).log(Level.SEVERE, null, ex);
        }

    }

}
